﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Web;

namespace DoNet.Helper
{
    public class ExcelHelper
    {
        #region  通过Excel导入数据
        /// <summary>
        /// 获取Excel连接字符串
        /// HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。
        /// 当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
        /// 当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
        /// 当 IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
        /// </summary>
        /// <param name="filePath">Excel完整路径</param>
        /// <returns></returns>
        public static string GetExcelConnString(string filePath)
        {
            string connStr = string.Empty;
            string fileType = Path.GetExtension(filePath);
            if (!string.IsNullOrWhiteSpace(fileType))
            {
                if (fileType == ".xls")
                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                else
                    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
            }
            return connStr;
        }

        /// <summary>
        /// 获取Excel内容。
        /// </summary>
        /// <param name="filePath">Excel完整路径</param>
        /// <returns></returns>
        public static DataSet GetTablesFromExcel(string filePath)
        {
            DataSet ds = null;
            if (File.Exists(filePath))
            {
                string connStr = GetExcelConnString(filePath);

                // 创建连接对象 
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    try
                    {
                        //打开连接
                        conn.Open();

                        // 得到包含数据架构的数据表 
                        using (DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                        {
                            ds = new DataSet();

                            string[] excelSheets = new string[schemaTable.Rows.Count];

                            // 添加工作表名称到字符串数组 
                            foreach (DataRow row in schemaTable.Rows)
                            {
                                string sheetTableName = row["TABLE_NAME"].ToString();
                                //过滤无效SheetName
                                if (sheetTableName.Contains("$") && sheetTableName.Replace("'", "").EndsWith("$"))
                                {
                                    sheetTableName = sheetTableName.Substring(0, sheetTableName.Length - 1);

                                    string strSQL = "Select * From [" + sheetTableName + "$]";
                                    using (OleDbDataAdapter da = new OleDbDataAdapter(strSQL, conn))
                                    {
                                        using (DataSet dsSheet = new DataSet())
                                        {
                                            da.Fill(dsSheet);
                                            using (DataTable dtSheet = dsSheet.Tables[0])
                                            {
                                                dtSheet.TableName = sheetTableName;
                                                ds.Tables.Add(dtSheet.Copy());
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        //异常详细信息: System.Data.OleDb.OleDbException: 外部表不是预期的格式。
                        //出现错误的原因：不是有效Excel格式文件
                        //1.不是Excel格式文件
                        //2.加密的Excel格式文件
                        throw ex;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 获取Excel指定工作表名称的数据
        /// </summary>
        /// <param name="filePath">Excel完整路径</param>
        /// <param name="sheetName">工作表名称，例：sheet1；默认为空，返回第一个sheet数据</param>
        /// <returns></returns>
        public static DataTable GetTableFromExcel(string filePath, string sheetName = null)
        {
            DataTable dt = null;
            using (DataSet ds = GetTablesFromExcel(filePath))
            {
                if (ds != null)
                {
                    if (string.IsNullOrWhiteSpace(sheetName))
                    {
                        dt = ds.Tables[0];
                    }
                    else
                    {
                        dt = ds.Tables[sheetName];
                    }
                }
            }
            return dt;
        }
        #endregion

        #region 将数据导出到Excel

        #region xml
        /// <summary>
        /// 去掉数据中 < >
        /// </summary>
        /// <param name="str">数据</param>
        /// <returns></returns>
        public static string replaceLegal(object str)
        {
            string s = string.Empty;
            if (str != null)
            {
                s = str.ToString();
            }
            if (!string.IsNullOrEmpty(s))
            {
                if (s.IndexOf("<") >= 0 || s.IndexOf(">") >= 0)
                {
                    s = s.Replace("<", "").Replace(">", "");
                }
            }
            return s;
        }

        /// <summary>
        /// 构建Excel头部
        /// </summary>
        /// <param name="sb"></param>
        /// <returns></returns>
        public static void BuildExcelXmlHeader(StringWriter sw)
        {
            try
            {
                //xml头部
                StringBuilder sb = new StringBuilder();
                sb.Append("<?xml version='1.0' ?>");
                sb.Append("<?mso-application progid='Excel.Sheet'?>");
                sb.Append("<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' ");
                sb.Append("xmlns:o='urn:schemas-microsoft-com:office:office' ");
                sb.Append("xmlns:x='urn:schemas-microsoft-com:office:excel' ");
                sb.Append("xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882' ");
                sb.Append("xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' ");
                sb.Append("xmlns:html='http://www.w3.org/TR/REC-html40'>");
                sw.Write(sb);
            }
            catch (Exception ex)
            {
                var err = ex.ToString();
                sw.Flush();
                sw.Close();
                sw.Dispose();
            }
        }

        /// <summary>
        /// 构建Excel样式
        /// </summary>
        /// <param name="sb"></param>
        /// <returns></returns>
        public static void BuildExcelXmlStyle(StringWriter sw)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("<Styles>");
                sb.Append("<Style ss:ID='Default' ss:Name='Normal'>");
                sb.Append("<Alignment ss:Vertical='Bottom'  ss:WrapText='1'/>");
                sb.Append("<Font  x:CharSet='136' x:Family='Roman'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sColumn' >");
                sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sHeader' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Center'   ss:WrapText='1'/>");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior ss:Color='#FFFF99' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                //sHeaderLeft标题居中
                sb.Append("<Style ss:ID='sHeaderLeft' >");
                sb.Append("<Alignment ss:Horizontal='Left' ss:Vertical='Center'   ss:WrapText='1'/>");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior ss:Color='#FFFF99' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sContent' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'   ss:WrapText='0' />"); //不折行显示 ss:WrapText='1' 折行 
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sContentWrap' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'   ss:WrapText='1' />"); //不折行显示 ss:WrapText='1' 折行 
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior/>");
                sb.Append("</Style>");

                //sContentRight 为右对齐的数据样式表，用于数字或金额栏的呈现
                sb.Append("<Style ss:ID='sContentRight' >");
                sb.Append("<Alignment ss:Horizontal='Right' ss:Vertical='Bottom'   ss:WrapText='0' />");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior/>");
                sb.Append("<NumberFormat ss:Format='#,##0'/>");
                sb.Append("</Style>");

                //sContentRightWrap 为右对齐的数据样式表，用于数字或金额栏的呈现
                sb.Append("<Style ss:ID='sContentRightWrap' >");
                sb.Append("<Alignment ss:Horizontal='Right' ss:Vertical='Bottom'   ss:WrapText='1' />");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior/>");
                sb.Append("<NumberFormat ss:Format='#,##0'/>");
                sb.Append("</Style>");

                //sContentVCenter 为右对齐且垂直居中对齐额数据样式表，用于数字或金额栏的呈现
                sb.Append("<Style ss:ID='sContentVCenter' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Center'   ss:WrapText='1' />"); //不折行顯示 ss:WrapText='1' 折行 
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior/>");
                sb.Append("</Style>");

                //sContentRightVCenter 为右对齐且垂直居中对齐额数据样式表，用于数字或金额栏的呈现
                sb.Append("<Style ss:ID='sContentRightVCenter' >");
                sb.Append("<Alignment ss:Horizontal='Right' ss:Vertical='Center'   ss:WrapText='1' />");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior/>");
                sb.Append("<NumberFormat ss:Format='#,##0'/>");
                sb.Append("</Style>");

                //sContentLeftVCenter 为左对齐且垂直居中对齐额数据样式表
                sb.Append("<Style ss:ID='sContentLeftVCenter' >");
                sb.Append("<Alignment ss:Horizontal='Left' ss:Vertical='Center'   ss:WrapText='1' />");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior/>");
                sb.Append("<NumberFormat ss:Format='#,##0'/>");
                sb.Append("</Style>");


                sb.Append("<Style ss:ID='sCell' >");
                sb.Append("<Alignment ss:Horizontal='Right' ss:Vertical='Bottom'   ss:WrapText='0'/>"); //不折行顯示 ss:WrapText='1' 折行 
                sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Bold='1'/>");
                sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sTitle' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'   ss:WrapText='0'/>"); //不折行顯示 ss:WrapText='1' 折行 
                sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='16' ss:Bold='1'/>");
                sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sAuthor' >");
                sb.Append("<Alignment ss:Horizontal='Right' ss:Vertical='Bottom'/>");
                sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='12' />");
                sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sshowdate' >");
                sb.Append("<Alignment ss:Horizontal='Left' ss:Vertical='Bottom'/>");
                sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='12' />");
                sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sOrgA' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>");
                sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='16' ss:Bold='1'/>");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior ss:Color='#FFFF99' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sOrgB' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>");
                sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='16' ss:Bold='1'/>");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior ss:Color='#C0FFC0' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sOrgC' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>");
                sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='16' ss:Bold='1'/>");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior ss:Color='#FFC080' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sSummary' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>");
                sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Bold='1'/>");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sNotice' >");
                sb.Append("<Alignment ss:Horizontal='Left' ss:Vertical='Bottom'/>");
                sb.Append("<Font  x:CharSet='136' x:Family='Roman' ss:Size='9' />");
                sb.Append("<Interior ss:Color='#FFFFFF' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sHeader1' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'   ss:WrapText='1'/>");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior ss:Color='#C0FFC0' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sHeader2' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'  ss:WrapText='1'/>");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior ss:Color='#FFC080' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("<Style ss:ID='sContent1' >");
                sb.Append("<Alignment ss:Horizontal='Center' ss:Vertical='Bottom'   ss:WrapText='1'/>");
                sb.Append("<Borders>");
                sb.Append("<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>");
                sb.Append("</Borders>");
                sb.Append("<Interior ss:Color='#99CCFF' ss:Pattern='Solid'/>");
                sb.Append("</Style>");

                sb.Append("</Styles>");
                sw.Write(sb);
            }
            catch (Exception ex)
            {
                var err = ex.ToString();
                sw.Flush();
                sw.Close();
                sw.Dispose();
            }
        }

        /// <summary>
        /// 构建Excel头部
        /// </summary>
        /// <param name="sb"></param>
        /// <returns></returns>
        public static void BuildExcelXmlFooter(StringWriter sw)
        {
            try
            {
                //xml尾部
                sw.Write("</Workbook>");
            }
            catch (Exception ex)
            {
                var err = ex.ToString();
                sw.Flush();
                sw.Close();
                sw.Dispose();
            }
        }

        /// <summary>
        /// 构建Excel的XML
        /// </summary>
        /// <param name="sbRow">数据Excel的xml字符串</param>
        /// <returns></returns>
        public static void BuildExcelXml(string sbRow, StringWriter sw, bool isStyle = false)
        {
            try
            {
                //xml头部
                BuildExcelXmlHeader(sw);

                //样式
                if (isStyle)
                {
                    BuildExcelXmlStyle(sw);
                }

                //数据
                sw.Write(sbRow);

                //xml尾部
                BuildExcelXmlFooter(sw);
            }
            catch (Exception ex)
            {
                var err = ex.ToString();
                sw.Flush();
                sw.Close();
                sw.Dispose();
            }
        }

        /// <summary>
        /// 获取Excel数据 Sheet
        /// 注意：如果数据量太大的话 会导致内存溢出  System.OutOfMemoryException
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="dt"></param>
        /// <param name="column_FieldTextWidth"></param>
        /// <returns></returns>
        public static string ExportStandard(string sheetName, DataTable dt, string[,,] column_FieldTextWidth = null, bool isStyle = false)
        {
            int rowCount = dt.Rows.Count;
            int colCount = column_FieldTextWidth.GetLength(0);

            StringBuilder sb = new StringBuilder();
            try
            {
                int i, j;

                sb.Append("<Worksheet ss:Name='");
                sb.Append(sheetName);
                sb.Append("'>");

                if (rowCount > 0)
                {
                    sb.Append("<Table x:FullColumns='1' x:FullRows='1'");
                    if (isStyle)
                    {
                        sb.Append(" ss:ExpandedColumnCount='");
                        sb.Append(colCount + 10);
                        sb.Append("' ss:ExpandedRowCount='");
                        sb.Append(rowCount + 10);
                        sb.Append("'");
                        sb.Append(" ss:StyleID='sColumn' ss:DefaultColumnWidth='42' ss:DefaultRowHeight='14.25'");
                    }
                    sb.Append(">");

                    //定义栏位
                    for (i = 0; i < colCount; i++)
                    {
                        sb.Append("<Column");
                        if (isStyle)
                        {
                            sb.Append(" ss:StyleID='sColumn'");
                            sb.Append(" ss:Width='");
                            sb.Append(column_FieldTextWidth[i, 0, 2]);
                            //sb.Append("px");
                        }
                        sb.Append("'/>");
                    }
                    //定义栏位名称
                    sb.Append("<Row>");
                    for (i = 0; i < colCount; i++)
                    {
                        sb.Append("<Cell");
                        if (isStyle)
                        {
                            sb.Append(" ss:StyleID='sHeader2'");
                        }
                        sb.Append(" ><Data ss:Type='String'>");
                        sb.Append(column_FieldTextWidth[i, 0, 1]);
                        sb.Append("</Data></Cell>");
                    }
                    sb.Append("</Row>");

                    //定义数据内容
                    for (j = 0; j < rowCount; j++)
                    {
                        sb.Append("<Row ss:Height='25'>");
                        for (i = 0; i < colCount; i++)
                        {
                            if (column_FieldTextWidth[i, 0, 0] != "")
                            {
                                if (dt.Columns[column_FieldTextWidth[i, 0, 0]].DataType.Equals(Type.GetType("System.Int32")))
                                {
                                    sb.Append("<Cell ss:StyleID='sContentRight'><Data ss:Type='Number'>");
                                    sb.Append(dt.Rows[j][column_FieldTextWidth[i, 0, 0]]);
                                    sb.Append("</Data></Cell>");
                                }
                                else
                                {
                                    sb.Append("<Cell ss:StyleID='sContentLeftVCenter'><Data ss:Type='String'>");
                                    sb.Append(replaceLegal(dt.Rows[j][column_FieldTextWidth[i, 0, 0]]));
                                    sb.Append("</Data></Cell>");
                                }
                            }
                            else
                            {
                                sb.Append("<Cell ss:StyleID='sContent'><Data ss:Type='String'></Data></Cell>");
                            }
                        }
                        sb.Append("</Row>");
                    }

                    sb.Append("<Row ss:Height='21'>");
                    sb.Append("<Cell ss:MergeAcross='");
                    sb.Append(colCount - 1);
                    sb.Append("'");
                    if (isStyle)
                    {
                        sb.Append(" ss:StyleID='sshowdate'");
                    }
                    sb.Append(" ><Data ss:Type='String'> </Data></Cell>");
                    sb.Append("</Row>");

                    //表尾
                    sb.Append("</Table>");
                }

                //Sheet结束
                sb.Append("</Worksheet>");
            }
            catch (Exception ex)
            {
                var err = ex.ToString();
                sb.Clear();
            }

            return sb.ToString();
        }

        /// <summary>
        /// 获取Excel数据 Sheet (微信交易/资金账单)
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="dt">明细数据</param>
        /// <param name="dtSum">合计数据</param>
        /// <param name="sb"></param>
        public static void ExportStandard(string sheetName, DataTable dt, DataTable dtSum, StringWriter sw)
        {
            try
            {
                sw.Write("<Worksheet ss:Name='" + sheetName + "'>");

                GetTable(dt, sw);

                GetTable(dtSum, sw);

                //Sheet结束
                sw.Write("</Worksheet>");
            }
            catch (Exception ex)
            {
                var err = ex.ToString();
                sw.Flush();
                sw.Close();
                sw.Dispose();
            }
        }

        /// <summary>
        /// 根据DataTable获取excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sb"></param>
        public static void GetTable(DataTable dt, StringWriter sw)
        {
            try
            {
                if (dt != null)
                {
                    int rowCount = dt.Rows.Count;
                    if (rowCount > 0)
                    {
                        sw.Write("<Table>");

                        //定义栏位
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sw.Write("<Column />");
                        }

                        //定义栏位名称
                        sw.Write("<Row>");
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sw.Write("<Cell><Data ss:Type='String'>" + dt.Columns[i].ColumnName + "</Data></Cell>");
                        }
                        sw.Write("</Row>");

                        //定义数据内容
                        for (int i = 0; i < rowCount; i++)
                        {
                            sw.Write("<Row>");
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                string columnData = replaceLegal(dt.Rows[i][j]);
                                sw.Write("<Cell><Data ss:Type='String'>" + columnData + "</Data></Cell>");
                            }
                            sw.Write("</Row>");
                        }

                        //表尾
                        sw.Write("</Table>");
                    }
                }
            }
            catch (Exception ex)
            {
                var err = ex.ToString();
                sw.Flush();
                sw.Close();
                sw.Dispose();
            }
        }
        #endregion xml

        #region TAB  换行 
        /// <summary>
        /// 将DataTable中的数据按流的方式 直接通过浏览器导出
        /// Excel2003 65536行
        /// Excel2007以上版本 1048576行
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="columnField">字段对应名称</param>
        /// <param name="sw"></param>
        public static void ExportExcel(DataTable dt, string[,] columnField, StringWriter sw)
        {
            var columnFieldCount = columnField.GetLength(0);
            try
            {
                string title = "";
                if (columnFieldCount > 0)
                {
                    for (int m = 0; m < columnFieldCount; m++)
                    {
                        title += columnField[m, 1] + "\t";
                    }
                }
                else
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        title += dt.Columns[i].ColumnName + "\t";
                    }
                }
                title = title.Substring(0, title.Length - 1) + "\n";
                sw.Write(title);

                foreach (DataRow row in dt.Rows)
                {
                    string line = "";
                    if (columnFieldCount > 0)
                    {
                        for (int m = 0; m < columnFieldCount; m++)
                        {
                            var columnName = columnField[m, 0];
                            line += row[columnName].ToString().Trim().Replace("\t", "").Replace("\n", "") + "\t";
                        }
                    }
                    else
                    {
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            var columnName = dt.Columns[i].ColumnName;
                            line += row[columnName].ToString().Trim().Replace("\t", "").Replace("\n", "") + "\t";
                        }
                    }
                    line = line.Substring(0, line.Length - 1) + "\n";
                    sw.Write(line);
                }
            }
            catch (Exception ex)
            {
                var err = ex.ToString();
                sw.Flush();
                sw.Close();
                sw.Dispose();
            }
        }
        #endregion

        //页面上导出Excel
        public static void OutExcelFile(string filename, StringWriter sw)
        {
            HttpContext httpContext = HttpContext.Current;

            HttpResponse response = httpContext.Response;

            response.Buffer = true;

            response.Clear();
            response.ClearHeaders();
            response.ClearContent();

            response.Charset = "UTF8";
            response.ContentEncoding = Encoding.UTF8;
            response.ContentType = "application/vnd.ms-excel";
            string browser = httpContext.Request.Browser.Browser;
            if (browser.Contains("InternetExplorer"))
                response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(filename, Encoding.UTF8));
            else
                response.AddHeader("Content-Disposition", "attachment; filename=" + filename);

            response.AddHeader("Content-Length", sw.GetStringBuilder().Length.ToString());  //记得返回该参数，否则会造成网络错误

            response.Flush();
            response.Write(sw);
            //response.Close();
            response.End();
        }



        /// <summary>
        /// /注：分浏览器进行编码（IE必须编码，FireFox不能编码，Chrome可编码也可不编码）
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="strHeaderText"></param>
        /// <param name="strFileName"></param>
        //public static void ExportByWeb(DataSet ds, string strHeaderText, string strFileName)
        //{
        //    HttpContext curContext = HttpContext.Current;

        //    curContext.Response.Clear();
        //    curContext.Response.ClearHeaders();
        //    curContext.Response.ClearContent();
        //    curContext.Response.ContentType = "application/vnd.ms-excel";
        //    curContext.Response.Charset = "UTF8";
        //    if (curContext.Request.UserAgent.ToLower().IndexOf("firefox", StringComparison.Ordinal) > 0)
        //    {
        //        curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + strFileName);
        //    }
        //    else
        //    {
        //        curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
        //    }

        //    curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        //    curContext.Response.ContentEncoding = Encoding.UTF8;
        //    curContext.Response.BinaryWrite(ExportDataSetToExcel(ds, strHeaderText).GetBuffer());
        //    curContext.Response.End();
        //}

        #endregion
    }
}
